﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data;
using System.Text.RegularExpressions;
using LOGISTCBUS.Entities;

namespace LOGISTCBUS.DAO
{
    public class BudgetDAO : BaseDAO
    {
        #region public methods
        public Boolean insert(Budget b)
        {
            var db = new Db();
            db.Connect();
            String sql = "insert into budgets (code, value, id_trip, description) ";
            sql += "values (@code, @value, @id_trip, @description);";
            var parameters = new Dictionary<String, Object>();
            parameters["@code"] = b.Code;
            parameters["@value"] = b.Value;
            parameters["@id_trip"] = b.Trip.Id;
            parameters["@description"] = b.Description;
            try
            {
                var sucess = db.Execute(sql, parameters);
                b.Id = Convert.ToInt32(db.Select("select max(b.id) from budgets b").Rows[0][0]);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Boolean update(Budget b)
        {
            var db = new Db();
            db.Connect();
            String sql = "update budgets set code = @code, value = @value, id_trip = @id_trip, description = @description where id= @id;";
            var parameters = new Dictionary<String, Object>();
            parameters["@code"] = b.Code;
            parameters["@value"] = b.Value;
            parameters["@id_trip"] = b.Trip.Id;
            parameters["@description"] = b.Description;
            parameters["@id"] = b.Id;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Budget selectById(Int32 id)
        {
            var db = new Db();
            db.Connect();
            var dt = db.Select("select * from budgets where id = " + id);
            db.Disconnect();
            if (dt == null || dt.Rows.Count <= 0)
                return null;
            DataRow r = dt.Rows[0];
            var obj = new Budget()
            {
                Id = Convert.ToInt32(r["id"]),
                Code = r["code"].ToString(),
                Value = Convert.ToDecimal(r["value"]),
                Trip = new Trip() { Id = Convert.ToInt32(r["id_trip"]) },
                Description = r["description"].ToString(),
                Removed = Convert.ToBoolean(r["removed"])
            };
            return obj;
        }

        public List<Budget> select()
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Budget>();
            var dt = db.Select("select * from budgets where removed = 0 order by id");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Budget()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Code = r["code"].ToString(),
                    Value = Convert.ToDecimal(r["value"]),
                    Trip = new Trip() { Id = Convert.ToInt32(r["id_trip"]) },
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public List<Finance> finance()
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Finance>();
            var dt = db.Select(@"select t.code, t.origin, t.destination, t.begin_date, t.description, b.value, 
                (select sum(value*quantity) from costs where id_trip=t.id) costs,
                (b.value - (select sum(value*quantity) from costs where id_trip=t.id)) lucro
                from trips t 
                inner join budgets b on b.id_trip = t.id
                where t.removed = 0 order by t.code");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Finance()
                {
                    CodigoViagem = r["code"].ToString(),
                    Custos = r["costs"].ToDecimal(),
                    Data = r["begin_date"].ToDateTime(),
                    Lucro = r["lucro"].ToDecimal(),
                    ValorTotal = r["value"].ToDecimal(),
                });
            }
            return lst;
        }

        public List<Budget> select(Int32 idTrip, Int32 page, Int32 pageSize)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Budget>();
            var sql = "select * from budgets where removed = 0";
            if (idTrip > 0)
                sql += " and id_trip = " + idTrip;
            sql += " order by id";
            var dt = db.Select(sql);
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Budget()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Code = r["code"].ToString(),
                    Value = Convert.ToDecimal(r["value"]),
                    Trip = new Trip() { Id = Convert.ToInt32(r["id_trip"]) },
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst.Skip((page - 1) * pageSize).Take(pageSize).ToList();
        }

        public Int32 getCount(Int32 idTrip)
        {
            var count = 0;
            var db = new Db();
            db.Connect();
            var lst = new List<Budget>();
            var sql = "select count(*) as qtd from budgets where removed = 0";
            if (idTrip > 0)
                sql += " and id_trip = " + idTrip;
            var dt = db.Select(sql);
            db.Disconnect();
            if (dt != null && dt.Rows.Count > 0)
            {
                var r = dt.Rows[0];
                count = Convert.ToInt32(r["qtd"]);
            }
            return count;
        }

        public List<Budget> select(String filter)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Budget>();
            var dt = db.Select("select * from budgets where removed = 0 and " + getConditions(filter, "description") + " order by id");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Budget()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Code = r["code"].ToString(),
                    Value = Convert.ToDecimal(r["value"]),
                    Trip = new Trip() { Id = Convert.ToInt32(r["id_trip"]) },
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public List<Budget> selectBudgets(Int32 idCustomer, Boolean hasNot)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Budget>();
            var dt = db.Select("select * from budgets b where b.id" + (hasNot ? " not " : " ") + "in (select bc.id_budget from budget_customers bc where bc.removed = 0 and bc.id_customer = " + idCustomer + ") order by b.code");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Budget()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Code = r["code"].ToString(),
                    Value = Convert.ToDecimal(r["value"]),
                    Trip = new Trip() { Id = Convert.ToInt32(r["id_trip"]) },
                    Description = r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public Boolean delete(Budget b)
        {
            var db = new Db();
            db.Connect();
            String sql = getSQLDeleteUpdate("budgets", "code");
            var parameters = new Dictionary<String, Object>();
            parameters["@id"] = b.Id;
            var sucess = db.Execute(sql, parameters);
            db.Commit();
            db.Disconnect();
            return sucess;
        }

        public Boolean delete(List<Budget> lst)
        {
            var db = new Db();
            db.Connect();
            var sucess = true;
            foreach (var b in lst)
            {
                String sql = getSQLDeleteUpdate("budgets", "code");
                var parameters = new Dictionary<String, Object>();
                parameters["@id"] = b.Id;
                sucess = db.Execute(sql, parameters);
            }
            db.Commit();
            db.Disconnect();
            return sucess;
        }

        public String selectNextCode()
        {
            var code = "000000/yyyy";
            var year = DateTime.Today.Year;
            var y1 = DateTime.Today.ToString("yyyy");
            var y2 = DateTime.Today.ToString("yy");
            var lstMatches = Regex.Matches(code, "[0]+");
            var number = 1;
            var db = new Db();
            db.Connect();
            try
            {
                var conds = "";
                foreach (Match m in lstMatches)
                    conds += " and substring(t.code, " + (m.Index + 1) + ", " + m.Length + ") >= '0' and substring(t.code, " + (m.Index + 1) + ", " + m.Length + ") <= '" + m.Value.Replace("0", "9") + "'";
                var sql = "select (cast(coalesce(substring(max(t.code), " + (lstMatches[0].Index + 1) + ", " + lstMatches[0].Length + "), 0) as integer) + 1) as code " +
                    "from budgets t where t.removed=0 " + conds + " and t.code like '" + code.Replace("0", "_").Replace("yyyy", y1).Replace("yy", y2) + "'";
                var dt = db.Select(sql);
                number = Convert.ToInt32(dt.Rows[0][0]);
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
            }
            foreach (Match m in lstMatches)
                code = code.Remove(m.Index, m.Length).Insert(m.Index, String.Format("{0:" + m.Value + "}", number));
            code = code.Replace("yyyy", DateTime.Today.ToString("yyyy")).Replace("yy", DateTime.Today.ToString("yy"));
            db.Disconnect();
            return code;
        }
        #endregion
    }
}
